<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>Views, triggers, transactions in SQLite</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="SQLite, SQL, database, view, trigger, transaction">
<meta name="description" content="In this part of the SQLite tutorial, we will
cover views, triggers, trasactions.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container2">

<div id="wide_ad" class="ltow">
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<div class="content2">


<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>Views, triggers, transactions</h1>


<p>
In this part of the SQLite tutorial, we will mention views, triggers and transactions.
</p>

<div class="big_hor">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* big_horizontal */
google_ad_slot = "2904953388";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>


<h2>Views</h2>

<p>
A view is a specific look on data in from one or more tables. 
It can arrange data in some specific order, higlight or hide some data. 
A view consists of a stored query accessible as a virtual table composed 
of the result set of a query. Unlike ordinary tables a view does not 
form part of the physical schema. It is a dynamic, virtual table computed 
or collated from data in the database.
</p>

<p>
In the next example, we create a simple view. 
</p>

<pre class="code">
sqlite> SELECT * FROM Cars;
Id          Name        Cost      
----------  ----------  ----------
1           Audi        52642     
2           Mercedes    57127     
3           Skoda       9000      
4           Volvo       29000     
5           Bentley     350000    
6           Citroen     21000     
7           Hummer      41400     
8           Volkswagen  21600  
</pre>

<p>
This is our data, upon which we create the view. 
</p>

<pre class="code">
sqlite> CREATE VIEW CheapCars AS SELECT Name FROM Cars WHERE Cost &lt; 30000;
sqlite> SELECT * FROM CheapCars;
Name      
----------
Skoda     
Volvo     
Citroen   
Volkswagen
</pre>

<p>
The CREATE VIEW statement is used to create a view. 
</p>

<pre class="code">
sqlite> .tables
Books         CheapCars     Friends       Names         Reservations
Cars          Customers     Log           Orders        Testing     
sqlite> DROP VIEW CheapCars;
sqlite> .tables
Books         Customers     Log           Orders        Testing     
Cars          Friends       Names         Reservations
</pre>

<p>
Technically a view is a virtual table. So we can list all views with a 
.tables command. To remove a view, we use
the DROP VIEW SQL statement.  
</p>


<h2>Triggers</h2>

<p>
<b>Triggers</b> are database operations that are automatically performed when a 
specified database event occurs. 
</p>

<p>
In the following example, we will use the Friends table and 
create a new Log table.
</p>

<pre class="code">
sqlite> CREATE TABLE Log(Id INTEGER PRIMARY KEY, OldName TEXT, 
   ...> NewName TEXT, Date TEXT);
</pre>

<p>
The Log table has a column for the old name and for the new name
of a friend. It also has a column for a timestamp.
</p>

<pre class="code">
CREATE TRIGGER mytrigger UPDATE OF Name ON Friends
BEGIN
INSERT INTO Log(OldName, NewName, Date) VALUES (old.Name, new.Name, datetime('now'));
END;
</pre>

<p>
We create a trigger called mytrigger with the CREATE TRIGGER statement. This trigger will
launch a INSERT statement whenever we update the name column of the Friends table. 
The INSERT statement will insert the old name, the new name and the time stamp into 
the Log table. The <i>old</i> and <i>new</i> are references to the row being modified.
</p>

<pre class="code">
sqlite> SELECT * FROM Friends;
Id          Name        Sex       
----------  ----------  ----------
1           Jane        F         
2           Thomas      M         
3           Franklin    M         
4           Elisabeth   F         
5           Mary        F         
6           Lucy        F         
7           Jack        M  
</pre>

<p>
This is our data. 
</p>

<p>
Next, we are going to update one row of the Friends table. 
</p>


<pre class="code">
sqlite> UPDATE Friends SET Name='Frank' WHERE Id=3;
</pre>

<p>
We update the third row of the table. The trigger is launched. 
</p>

<pre class="code">
sqlite> SELECT * FROM Log;
Id          OldName     NewName     Date               
----------  ----------  ----------  -------------------
1           Franklin    Frank       2013-01-09 23:38:29
</pre>

<p>
We check the Log table. This log confirms the update operation we performed. 
</p>


<h2>Transactions</h2>

<p>
A <b>transaction</b> is an atomic unit of database operations against the data
in one or more databases. The effects of all the SQL statements in a transaction
can be either all committed to the database or all rolled back.
</p>

<p>
In SQLite, any command other than the SELECT will start an implicit transaction. 
Manual transactions are started with the BEGIN TRANSACTION statement
and finished with the COMMIT or ROLLBACK statements.
</p>

<pre class="code">
BEGIN TRANSACTION;
CREATE TABLE Test(Id integer NOT NULL);
INSERT INTO Test VALUES(1);
INSERT INTO Test VALUES(2);
INSERT INTO Test VALUES(3);
INSERT INTO Test VALUES(NULL);
COMMIT;
</pre>

<p>
Here we have a sample transaction. A transaction begins with BEGIN TRANSACTION and
ends with COMMIT. 
</p>

<p>
We have a NOT NULL constraint set on the Id column. Thus, the fourth insert 
will not succeed. SQLite does transactions specifically. For some errors, 
it reverts all changes. For others, it reverts only the last statement and leaves
other changes intact. In our case, the table is created and the first three 
inserts are written into the table. The fourth one is not. 
</p>

<p>
Say, we already had an empty table named Test. Executing the above transaction 
would fail completely. No changes would be written. If we changed the CREATE TABLE 
statement into CREATE TABLE IF NOT EXISTS, the first three statements would execute. 
</p>

<pre class="code">
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS Test(Id integer NOT NULL);
INSERT INTO Test VALUES(1);
INSERT INTO Test VALUES(2);
INSERT INTO Test VALUES(3);
INSERT INTO Test VALUES(NULL);
ROLLBACK;
</pre>

<p>
A transaction can end with a COMMIT or a ROLLBACK statement.
The ROLLBACK reverts all changes. 
</p>

<p>
In this part of the SQLite tutorial, we have worked with views, triggers and 
transactions in SQLite. 
</p>

<br>
<div class="center"> 
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* horizontal */
google_ad_slot = "1734478269";
google_ad_width = 468;
google_ad_height = 60;
//-->
</script> 
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> 
</script> 
</div> 
<br>

<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡ 
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified January 9, 2013  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

